package de.fhhof.vinf.ssp.ticketcenter.beans;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.naming.NamingException;

import de.fhhof.vinf.ssp.ticketcenter.helpers.DatabaseManager;

public class TicketCategoryBean {
	
	private Integer ticketCatId;
	private Integer eventId;
	private double price;
	private Integer ticketAmount;
	private String name;
	private boolean isToDelete = false; //wird ein update in einer EventBean durchgefuehrt und ist isToDelete true wird die TicketCategory mit dem Update aus der DB und EventBean geloescht
	
	public boolean isToDelete() {
		return isToDelete;
	}
	public void setToDelete(boolean isToDelete) {
		this.isToDelete = isToDelete;
	}
	public Integer getTicketCatId() {
		return ticketCatId;
	}
	public void setTicketCatId(Integer ticketCatId) {
		this.ticketCatId = ticketCatId;
	}
	public Integer getEventId() {
		return eventId;
	}
	public void setEventId(Integer eventId) {
		this.eventId = eventId;
	}
	public double getPrice() {
		return price;
	}
	public void setPrice(double price) {
		this.price = price;
	}
	public Integer getTicketAmount() {
		return ticketAmount;
	}
	public void setTicketAmount(Integer ticketAmount) {
		this.ticketAmount = ticketAmount;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	
	/**
	 * Liest die Daten der Ticketkategorie aus der Datenbank.
	 * @param ticketCatId	ticket_category_id der Ticketkategorie
	 * @throws NamingException
	 * @throws SQLException
	 * @throws ClassNotFoundException 
	 */
	public void readFromDB(Integer ticketCatId) throws NamingException, SQLException, ClassNotFoundException{
		Connection con = DatabaseManager.getConnection();
		Statement stmt = con.createStatement();
		ResultSet res = stmt.executeQuery("SELECT * FROM ticket_categorys WHERE ticket_category_id =" + ticketCatId + ";");
		while (res.next()) {
			this.setName(res.getString("name"));
			this.setTicketCatId(ticketCatId);
			this.setEventId(res.getInt("event_id"));
			this.setPrice(res.getDouble("price"));
			this.setTicketAmount(res.getInt("ticket_amount"));
		}
		con.close();
	}
	
	public void updateInDB() throws NamingException, SQLException {
		Connection con = DatabaseManager.getConnection();
		Statement stmt = con.createStatement();
		if(getTicketCatId()==null){
			writeCategoryInDB();
		}else{
			ResultSet res = stmt.executeQuery("SELECT * FROM ticket_categorys WHERE ticket_category_id =" + ticketCatId + ";");
			while (res.next()) {
				if(!res.getString("name").equals(getName()) || res.getDouble("price")!=getPrice()){
					Statement stmt2 = con.createStatement();
					stmt2.executeUpdate("update ticket_categorys set name = '"+getName()+"' , price = "+getPrice()+" where ticket_category_id =" + ticketCatId + ";");
				}
				if(getTicketAmount()!= res.getInt("ticket_amount")){
					Statement stmt3 = con.createStatement();
					stmt3.executeUpdate("update ticket_categorys set ticket_amount = "+getTicketAmount()+" where ticket_category_id =" + ticketCatId + ";");
					deleteTickets();
					generateTickets();
				}
				
			}
		}
			
			
		
		con.close();
		
	}
	
	
	/**
	 * Schreibt die  Kategorien in die DB 
	 * 
	 * @param con
	 * @throws NamingException
	 * @throws SQLException
	 */
	public void writeCategoryInDB() throws NamingException,
	SQLException {
		Connection con = DatabaseManager.getConnection();
		Statement stmt = con.createStatement();

			stmt.executeUpdate("insert into studienarbeit.ticket_categorys (event_id,price,ticket_amount,name) "
				+ "values ("
				+ getEventId()
				+ ","
				+ getPrice()
				+ ","
				+ getTicketAmount() + ",'" + getName() + "');");
		// Categoty-ID auslesen und in Bean speichern
		ResultSet res = stmt
				.executeQuery("SELECT ticket_category_id FROM studienarbeit.ticket_categorys WHERE name='"
						+ this.getName() + "';");
		while (res.next()) {
			this.setTicketCatId(res.getInt(1));
		}
		con.close();
		generateTickets();
	}

	
	/**
	 * erstellt Tickets in der Tabelle Tickets mit der catId dieser Bean 
	 * in der Menge amount dieser Bean
	 * @throws SQLException
	 */
	public void generateTickets() throws SQLException {
		Thread thread = new Thread(new Runnable() {
			public void run() {
				Connection con;
				try {
					con = DatabaseManager.getConnection();
					Statement stmt = con.createStatement();
						for (int i = 0; i < getTicketAmount(); i++) {
							stmt.execute("insert into studienarbeit.tickets (ticket_category_id)"
									+ "values("
									+ getTicketCatId()
									+ ");");
						}
					con.close();
				} catch (Exception e) {
					System.out.println(e);					
				}
			}
		});
		thread.start();
	}
	
	
	/**
	 * loescht alle mit dieser Bean verbundenen Tickets (alle Tickets der catId dieser Bean)
	 * @throws NamingException
	 * @throws SQLException
	 */
	public void deleteTickets() throws NamingException, SQLException {
		Connection con = DatabaseManager.getConnection();
		Statement stmt = con.createStatement();
		stmt.executeUpdate("DELETE FROM tickets WHERE ticket_category_id = "+getTicketCatId()+";");
		con.close();
	}
	
	public void deleteCategory() throws NamingException, SQLException{
		deleteTickets();
		Connection con = DatabaseManager.getConnection();
		Statement stmt = con.createStatement();
		stmt.executeUpdate("DELETE FROM ticket_categorys WHERE ticket_category_id = "+getTicketCatId()+";");
		con.close();
	}

}
